<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Leaves_db extends CI_Model
{
	function getLeaves($data=false, $id=false)
	{
		if($data)
			$this->db->where($data);
		if($id)
			$this->db->where('id != ',$id);
		$query = $this->db->get('leaves');
		return $query->result_array();
	}
	
	function getLeaveScope($data=false)
	{
		if($data)
			$this->db->where($data);
		$this->db->join('employee_status b', 'b.employee_status_id = a.employee_status_id', 'left');
		$this->db->select('a.leave_scope_id, a.employee_status_id, b.es_name');
		$query = $this->db->get('leave_scope a');
		return $query->result_array();
	}
	
	function save($data, $item=false)
	{	
		if (!$item)
		{			
			$this->db->trans_start();
			$this->db->insert('leaves', $data);
			$item = $this->db->insert_id();
			$this->db->trans_complete(); 					
		}
		else
		{
			$this->db->trans_start();
			$this->db->where('leave_id', $item);
			$this->db->update('leaves', $data);		
			$this->db->trans_complete();
		}
		return $item;	
	}
	
	function delete($id)
	{
		if(count($id)>1)
			$this->db->where_in('leave_id',$id);
		else
			$this->db->where('leave_id',$id[0]);

		$this->db->delete('leaves'); 
		return $id;
	}
	
	function getList()
	{		
		$sortname = $this->input->post('sortname');
		$sortorder = $this->input->post('sortorder');
		$page = $this->input->post('page');
		$rp = $this->input->post('rp');		
		
		//$query = $this->input->post('query');
		//$qtype = $this->input->post('qtype');
		$squery = $this->input->post('squery');
		
		$cond = "";

		if (!$sortname) $sortname = 'leave_name';
		if (!$sortorder) $sortorder = 'ASC';
		
		
		if (!$page) $page = 1;
		if (!$rp) $rp = 40;
		
		$start = (($page-1) * $rp);
		
		$this->db->start_cache();
		if ($squery) $this->db->where("(LOWER(a.leave_name) LIKE '%$squery%' OR LOWER(a.leave_code) LIKE '%$squery%' )");
		$this->db->join('genders b', 'b.gender_id = a.gender_id', 'left');
		$this->db->join('leave_scope c', 'c.leave_scope_id = a.leave_scope_id', 'left');
		$this->db->join('employee_status d', 'd.employee_status_id = c.employee_status_id', 'left');
		$this->db->select("leave_id as pkey, leave_id as test, leave_code, leave_name, no_of_days, CASE a.leave_scope_id WHEN 0 THEN 'All' ELSE d.es_name END AS es_name, CASE a.gender_id WHEN 0 THEN 'All' ELSE b.gender_name END AS gender_name",false);
		$this->db->order_by($sortname,$sortorder);
		$this->db->limit($rp, $start);		
		$query = $this->db->get("leaves a");
		//$num = $this->db->count_all_results();
		$num = count($query->result_array());

		$this->db->flush_cache();
		
		
		$rows = array();
		if (isset($query))
		{			 
			foreach($query->result_array() as $row):			
				$key = key($row);
				$id = $row[$key];
				unset($row[$key]);
				$cell = array();
				foreach ($row as $item) { $cell[] = $item; }
				$rows[] = array( "id" => $id, "cell" => $cell );				
			endforeach; 
		}
		
		$data['rows'] = $rows;
		$data['page'] = $page;
		$data['total'] = $num;
		return $data;			
		
	}
	
	function addLeavesToEmployees($leave_id, $status_id, $gender_id, $created_by)
	{
		$status_cond = ($status_id) ? "a.employee_status_id = $status_id" : "a.employee_status_id > 0";
		$gender_cond = ($gender_id) ? "a.gender_id = $gender_id" : "a.gender_id > 0";
		$this->db->query("
			INSERT INTO employee_leaves (user_id, leave_id, available_days, created, created_by)
			SELECT id, $leave_id as leave_id, no_of_days as available_days, now() as created, $created_by as created_by
			FROM users a
			LEFT JOIN genders b ON b.gender_id = a.gender_id
			LEFT JOIN leaves c ON c.leave_id = $leave_id
			WHERE $status_cond
			AND $gender_cond
			AND a.id NOT IN (SELECT user_id FROM employee_leaves WHERE leave_id = $leave_id)"
		);
		//$query = $this->db->get();
	}
	
	function addLeavesToEmployee($status_id, $gender_id, $created_by, $user_id)
	{
		$this->db->query("
			INSERT INTO employee_leaves (user_id, leave_id, available_days, created, created_by)
			SELECT $user_id, leave_id, no_of_days, now(), $created_by
			FROM leaves a
			LEFT JOIN leave_scope b ON b.leave_scope_id = a.leave_scope_id
			WHERE a.leave_scope_id = 0 OR b.employee_status_id = $status_id
			AND (a.gender_id = $gender_id  OR a.gender_id = 0)"
		);
		//$query = $this->db->get();
	}

}

?>